create table History(
event_id        number,
commutator_id    number,
event_date  date,
event     varchar2(20)
);

create table commutators(
commutator_id number,
commutator_type varchar2(10),
speed number,
ports number,
quantity number,
commutator_location varchar2(20),
constraint commutators_PK primary key(commutator_id)
);

create sequence commutators_id;
create sequence event_id;
alter table history add constraint history_pk primary key(event_id);
alter table history add constraint history_fk foreign key(commutator_id)references commutators(commutator_id);

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,20,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',10,16,4,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,12,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,8,'A Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,3,'B Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',10,16,1,'A Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',100,32,5,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',100,32,2,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,4,'A Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',100,16,12,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,8,'A Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',10,16,1,'B Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'hub',10,8,4,'B Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',100,16,3,'B Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',100,32,1,'Main Office');

insert into commutators(commutator_id,commutator_type,speed,ports,quantity,commutator_location)
values(commutators_id.nextval,'switch',100,16,4,'A Office');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,62,to_date('7/25/2001','mm/dd/yy'),'adding on balance')

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,63,to_date('7/26/2001','mm/dd/yy'),'adding on balance');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,64,to_date('8/14/2001','mm/dd/yy'),'adding on balance');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,65,to_date('8/21/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,66,to_date('8/23/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,67,to_date('9/1/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,68,to_date('9/3/2001','mm/dd/yy'),'adding on balance');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,69,to_date('9/7/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,70,to_date('9/14/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,71,to_date('9/21/2001','mm/dd/yy'),'adding on balance');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,72,to_date('9/25/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,73,to_date('10/2/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,74,to_date('10/8/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,75,to_date('10/10/2001','mm/dd/yy'),'installing');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,76,to_date('10/12/2001','mm/dd/yy'),'adding on balance');

insert into history(event_id,commutator_id,event_date,event) 
values(event_id.nextval,77,to_date('10/14/2001','mm/dd/yy'),'installing');



SELECT commutator_location, count(commutator_id)
from commutators
group by commutator_location,speed
having speed<=10;

CREATE OR REPLACE TRIGGER test_trg
BEFORE INSERT 
ON history 
FOR EACH ROW
WHEN (new.event_id is null)
DECLARE v_id history.event_id%TYPE; 
BEGIN
SELECT sqn_event_id.nextval INTO v_id FROM DUAL;
:new.event_id := v_id; 
END test_trg;

insert into history(commutator_id) SELECT commutator_id FROM commutators where speed<=10;

update history
set 
event = 'write-off',
event_date = sysdate-65535
where commutator_id = any (select commutator_id from commutators where speed<=10)







----
create view info as

select commutator_type,speed,ports,quantity,history.event
from history left join commutators on history.commutator_id = commutators.commutator_id
where history.event='installing' or history.event='adding on balance'

create table off_archive (
event_id        number,
commutator_id    number,
event_date  date,
event     varchar2(20),

arch_date date default sysdate
);

insert into off_archive(event_id,commutator_id,event_date,event) 
SELECT * FROM history
where history.event = 'write-off'

delete from history
where event='write-off'

insert into commutators(commutator_id) values (commutators_id.nextval);


update commutators set 
ports = (select sum(ports) from history left join commutators on history.commutator_id = commutators.commutator_id where history.event='write-off' and commutators.commutator_location='A Office'),
commutators.commutator_location = 'A Office',
commutators.quantity=1,
commutators.speed=100,
commutators.commutator_type='switch'
where commutator_id = 113

insert into history (commutator_id,event_date,event)
values(113,sysDATE,'adding on balance')

insert into commutators(commutator_id) values (commutators_id.nextval);


update commutators set 
ports = (select sum(ports) from history left join commutators on history.commutator_id = commutators.commutator_id where history.event='write-off' and commutators.commutator_location='B Office'),
commutators.commutator_location = 'B Office',
commutators.quantity=1,
commutators.speed=100,
commutators.commutator_type='switch'
where commutator_id = 114;

insert into history (commutator_id,event_date,event)
values(114,sysDATE,'adding on balance');


insert INTO history (commutator_id,event)
values (113, 'installing');

insert INTO history (commutator_id,event)
values (114, 'installing');

update history 
set event_date = (select event_date from history where commutator_id=113)

update history 
set event_date = (select event_date from history where commutator_id=114)

